﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using SAAC.DTO;
using System.Data;

namespace SAAC.DAL
{
	public class ProfessorDAL : DALBase
	{

		public ProfessorDTO Select(ProfessorDTO dto, string order = null)
		{
			try
			{
				string sql = "CALL sp_professor_sel(@matricula,@nome,@colegiadoId,@comissaoId,@usuarioId,@order,@limit);";

				if (dto.Colegiado == null) dto.Colegiado = new ColegiadoDTO();
				if (dto.Comissao == null) dto.Comissao = new ComissaoDTO();
				if (dto.Usuario == null) dto.Usuario = new UsuarioDTO();

				MySqlCommand cmd = new MySqlCommand(sql, con);
				cmd.Parameters.AddWithValue("@matricula", dto.Matricula);
				cmd.Parameters.AddWithValue("@nome", dto.Nome);
				cmd.Parameters.AddWithValue("@colegiadoId", dto.Colegiado.Id);
				cmd.Parameters.AddWithValue("@comissaoId", dto.Comissao.Id);
				cmd.Parameters.AddWithValue("@usuarioId", dto.Usuario.Id);
				cmd.Parameters.AddWithValue("@order", order);
				cmd.Parameters.AddWithValue("@limit", 1);

				con.Open();
				MySqlDataReader dr;

				dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

				if (dr.Read())
				{
					dto.Matricula = Convert.ToInt32(dr["matricula"]);
					dto.Nome = dr["nome"].ToString();
					dto.Colegiado.Id = DBNull.Value.Equals(dr["Colegiado_idColegiado"]) ? (int?)null : Convert.ToInt32(dr["Colegiado_idColegiado"]);
					dto.Comissao.Id = DBNull.Value.Equals(dr["Comissao_idComissao"]) ? (int?)null : Convert.ToInt32(dr["Comissao_idComissao"]);
					dto.Usuario.Id = DBNull.Value.Equals(dr["Usuario_idUsuario"]) ? (int?)null : Convert.ToInt32(dr["Usuario_idUsuario"]);
				}
				else dto = null;

				return dto;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				con.Close();
			}
		}

		public List<ProfessorDTO> SelectAll(ProfessorDTO dto, string order = null, int limit = 1000)
		{
			try
			{
				string sql = "CALL sp_professor_sel(@matricula,@nome,@colegiadoId,@comissaoId,@usuarioId,@order,@limit);";

				if (dto.Colegiado == null) dto.Colegiado = new ColegiadoDTO();
				if (dto.Comissao == null) dto.Comissao = new ComissaoDTO();
				if (dto.Usuario == null) dto.Usuario = new UsuarioDTO();
				var lista = new List<ProfessorDTO>();

				MySqlCommand cmd = new MySqlCommand(sql, con);
				cmd.Parameters.AddWithValue("@matricula", dto.Matricula);
				cmd.Parameters.AddWithValue("@nome", dto.Nome);
				cmd.Parameters.AddWithValue("@colegiadoId", dto.Colegiado.Id);
				cmd.Parameters.AddWithValue("@comissaoId", dto.Comissao.Id);
				cmd.Parameters.AddWithValue("@usuarioId", dto.Usuario.Id);
				cmd.Parameters.AddWithValue("@order", order);
				cmd.Parameters.AddWithValue("@limit", limit);


				con.Open();
				MySqlDataReader dr;

				dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

				while (dr.Read())
				{
					var item = new ProfessorDTO();
					if (item.Colegiado == null) item.Colegiado = new ColegiadoDTO();
					if (item.Comissao == null) item.Comissao = new ComissaoDTO();
					if (item.Usuario == null) item.Usuario = new UsuarioDTO();

					item.Matricula = Convert.ToInt32(dr["matricula"]);
					item.Nome = dr["nome"].ToString();
					item.Colegiado.Id = DBNull.Value.Equals(dr["Colegiado_idColegiado"]) ? (int?)null : Convert.ToInt32(dr["Colegiado_idColegiado"]);
					item.Comissao.Id = DBNull.Value.Equals(dr["Comissao_idComissao"]) ? (int?)null : Convert.ToInt32(dr["Comissao_idComissao"]);
					item.Usuario.Id = DBNull.Value.Equals(dr["Usuario_idUsuario"]) ? (int?)null : Convert.ToInt32(dr["Usuario_idUsuario"]);
					lista.Add(item);
				}

				return lista;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				con.Close();
			}
		}

		public void Insert(ProfessorDTO dto)
		{
			try
			{
				String sql = @"CALL sp_professor_ins(@matricula,@nome,@colegiadoId,@comissaoId,@usuarioId);";

				MySqlCommand cmd = new MySqlCommand(sql, con);

				cmd.Parameters.AddWithValue("@matricula", dto.Matricula);
				cmd.Parameters.AddWithValue("@nome", dto.Nome);
				cmd.Parameters.AddWithValue("@colegiadoId", dto.Colegiado.Id);
				cmd.Parameters.AddWithValue("@comissaoId", dto.Comissao.Id);
				cmd.Parameters.AddWithValue("@usuarioId", dto.Usuario.Id);

				con.Open();
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				con.Close();
			}
		}

		public void Update(ProfessorDTO dto)
		{
			try
			{
				String sql = "CALL sp_professor_upd(@matricula,@nome,@colegiadoId,@comissaoId,@usuarioId);";

				MySqlCommand cmd = new MySqlCommand(sql, con);

				cmd.Parameters.AddWithValue("@matricula", dto.Matricula);
				cmd.Parameters.AddWithValue("@nome", dto.Nome);
				cmd.Parameters.AddWithValue("@colegiadoId", dto.Colegiado.Id);
				cmd.Parameters.AddWithValue("@comissaoId", dto.Comissao.Id);
				cmd.Parameters.AddWithValue("@usuarioId", dto.Usuario.Id);

				con.Open();
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				con.Close();
			}
		}

		public void Delete(ProfessorDTO dto)
		{
			try
			{
				String sql = "CALL up_professor_del(@matricula);";

				MySqlCommand cmd = new MySqlCommand(sql, con);

				cmd.Parameters.AddWithValue("@matricula", dto.Matricula);
				con.Open();
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				con.Close();
			}
		}


	}
}
